package com.artfii.fluentsql.core;

import com.artfii.fluentsql.core.exception.FluentSqlException;
import com.artfii.fluentsql.core.jdbc.ConnectionBuilder;
import com.artfii.fluentsql.core.jdbc.FluentConnection;
import com.artfii.fluentsql.core.jdbc.FluentSqlQuery;
import com.artfii.fluentsql.core.jdbc.Qe;
import com.artfii.fluentsql.core.tools.*;
import org.osgl.util.S;

import java.util.*;
import java.util.logging.Logger;

import static com.artfii.fluentsql.core.BaseQuery.Opt.*;

/**
 * Func : 查询 API 的基类
 *
 * @author: leeton on 2019/6/21.
 */
public abstract class BaseQuery<T> implements Query {

    protected static final Logger logger = Logger.getLogger("BaseQuery");
    protected Integer id = 0;
    protected Class<T> clz;
    protected String mainTableName = "";
    protected Map<String, StringBuffer> templateMap = initTemplateMap();
    protected String symbolsql = "";  // 还没有设值的带占位符的SQL
    protected Map<String, Object> where_params = new HashMap<>(30);//where条件入参
    protected Map<String, Object> body_params = BeanMapUtils.newSortMap();//save/update(body)入参
    protected List<Map<Integer, Object>> batchParamList = new ArrayList<>(); //批量写入的入参
    protected Map<String, Integer> paramsNameIdx = new HashMap<>(30); //字段名称在SQL语句的位置序号
    protected boolean checkSqlHack = false;
    private String link; // 连接符 AND/OR
    protected boolean show_debug_sql = false;
    protected boolean test_mode=false; //测试模式
    protected String db_type_name = ""; //数据库类型
    protected boolean onlyConition = false; //只限 CONDITION 语句
    protected boolean isCount = false; //是否执行COUNT SQL
    protected boolean skipNullVol = true; // 更新字段时空值是否写入
    protected ConnectionBuilder.SqlEngine engine = ConnectionBuilder.SqlEngine.fluentsql; //具体使用的底层 SQL-ENGINE
    protected FluentSqlQuery fluentSqlQuery;

    public BaseQuery() {
        this.test_mode = true; //没有传入实体类，就当做是测试模式
        this.show_debug_sql = true;
    }
    public BaseQuery(Class<T> clz) {
        init(clz, getTableName(clz),null);
    }

    public BaseQuery(Class<T> clz, FluentConnection connection) {
        init(clz, getTableName(clz),connection);
    }

    private BaseQuery<T> init(Class<T> clz, String mainTableName,FluentConnection connection) {
        this.id = hashCode();
        this.clz = clz;
        this.mainTableName = mainTableName;
        this.fluentSqlQuery = new FluentSqlQuery(connection);
        return this;
    }

    /**
     * 配置具体使用的底层 SQL-ENGINE
     * @param engine
     */
    public abstract void engine();

    protected String buildCondition() {//只构建表达式
        return this.build();
    }

    public BaseQuery<T> notSkipNullVol() { //用于一些要强制写入 NULL 值的字段
        skipNullVol = false;
        return this;
    }

    public BaseQuery<T> select(String... vals) {
        if (vals.length > 0) {
            for (String v : vals) {
                selectAs(mainTableName, v, null);
            }
        }
        return this;
    }

    public BaseQuery<T> selectAs(Class otherClz, String v, String as) {
        selectAs(getTableName(otherClz), v, as);
        return this;
    }

    public BaseQuery<T> selectAs(String column, String as) {
        return selectAs(mainTableName, column, as);
    }

    public BaseQuery<T> select(Attr.Property<T, ?> func, Spell spell) {
        return select(spell, func);
    }

    public BaseQuery<T> select(Spell spell, Attr.Property<T, ?> func) {
        Attr f = Attr.parse(func);
        String underline_column = f.getColumn();
        String camel_column = f.getName();
        if (underline_column.equals(camel_column)) {
            return selectAs(mainTableName, underline_column, null);
        }
        String as = Spell.to(null, spell, underline_column, f.getName());
        selectAs(mainTableName, underline_column, as);
        return this;
    }

    public BaseQuery<T> select(Attr attr, Spell spell) {
        return select(spell, attr);
    }

    public BaseQuery<T> select(Spell spell, Attr attr) {
        String column = attr.getColumn();
        String as = Spell.to(null, spell, column, attr.getName());
        selectAs(attr.getTableName(), column, as);
        return this;
    }

    public BaseQuery<T> select(Spell spell, Attr... attrs) {
        for (Attr attr : attrs) {
            String column = attr.getColumn();
            String as = Spell.to(null, spell, column, attr.getName());
            selectAs(attr.getTableName(), column, as);
        }
        return this;
    }

    public BaseQuery<T> selectAs(String table, String column, String as) {
        StringBuffer select = templateMap.get("select");
        if (select.length() == 0) {
            select.append(SELECT.symbol);
        }
        if (select.length() > SELECT.symbol.length()) {
            select.append(",");
        }
        select.append(table).append(".").append(column);
        if (null != as && "" != as) {
            select.append(AS.symbol).append(as);
        }
        return this;
    }

    public BaseQuery<T> select(Qf... qfs) {
        StringBuffer select = templateMap.get("select");
        if (select.length() == 0) {
            select.append(SELECT.symbol);
        }
        for (Qf qf : qfs) {
            if (select.length() > SELECT.symbol.length()) {
                select.append(",");
            }
            select.append(qf.sql);
        }
        return this;
    }

    public BaseQuery<T> fromSubSelect(BaseQuery<T> subSelect) {
        fromSubSelect(subSelect, subSelect.mainTableName);
        return this;
    }

    public BaseQuery<T> fromSubSelect(BaseQuery<T> subSelect, String as) {
        StringBuffer from = templateMap.get("from");
        from.append(FROM.symbol).append("(");
        from.append(subSelect.build());
        from.append(")");
        if (StringKit.isNotBlank(as)) {
            from.append(AS.symbol).append(as);
            this.mainTableName = as;
        }
        return this;
    }

    public BaseQuery<T> fromAs(String as) {
        StringBuffer from = templateMap.get("from");
        if (0 == from.length()) {
            from.append(FROM.symbol);
            from.append("`").append(this.mainTableName).append("`");
        } else {
            from.append(" ,");
            from.append(" `").append(this.mainTableName).append("`");
        }
        from.append(AS.symbol).append(as);
        return this;
    }

    public BaseQuery<T> sum(String column, Class... otherClz) {
        String otherTable = getTablePrev(otherClz);
        if (mainTableName.equals(otherTable)) {
            return sumAs(column, column, otherTable);
        } else {
            return sumAs(column, otherTable +"_"+column, otherTable);
        }
    }

    public BaseQuery<T> sumAs(String column, String as, String... table) {
        StringBuffer select = templateMap.get("select");
        if (select.length() > 0) {
            select.append(",SUM(");
        } else {
            select.append(" SELECT SUM(");
        }
        if (null != table && table.length > 0) {
            select.append(table[0]).append(".");
        }
        select.append(column).append(") AS ").append(as);
        return this;
    }

    public BaseQuery<T> sumCase(String caseField, Object eqVal, String sumField, String asFiled, Class... otherClz) {
        StringBuffer select = templateMap.get("select");
        if (select.length() == 0) {
            select.append("SELECT ");
        } else {
            select.append(" ,");
        }
        select.append(" SUM(IFNULL(");
        String table = getTablePrev(otherClz);
        select.append("CASE ")
                .append(table).append(".").append(caseField)
                .append(" WHEN ").append(eqVal)
                .append(" THEN ")
                .append(table).append(".").append(sumField)
                .append(" END, 0)) AS ").append(asFiled);
        return this;
    }

    public BaseQuery<T> caseAs(String caseField, Object eqVal, String targetField, String asFiled, Class... otherClz) {
        StringBuffer select = templateMap.get("select");
        if (select.length() == 0) {
            select.append("SELECT ");
        } else {
            select.append(" ,");
        }
        select.append(" (CASE ");
        String table = getTablePrev(otherClz);
        select.append(table).append(".").append(caseField)
                .append(" WHEN ").append(eqVal)
                .append(" THEN ")
                .append(table).append(".").append(targetField)
                .append(" END) AS ").append(asFiled);
        return this;
    }

    protected BaseQuery delete() {
        StringBuffer del = templateMap.get("del");
        del.append(DEL.symbol).append(FROM.symbol).append(mainTableName);
        return this;
    }

    public BaseQuery<T> leftJoin(String joinTableName, String joinTableKey, String mainTableKey) {
        StringBuffer join = templateMap.get("join");
        if (null == mainTableName || "".equals(mainTableName)) throw new FluentSqlException("主表不能为空。");
        join.append(LEFTJOIN.symbol)
                .append("`")
                .append(joinTableName)
                .append("`")
                .append(ON.symbol)
                .append(this.mainTableName)
                .append(".")
                .append(mainTableKey)
                .append(" = ")
                .append(joinTableName)
                .append(".")
                .append(joinTableKey);
        return this;
    }

    /**
     * 约定:关联从表的字段名默认为: 从表字+"_id",全小写 underline 风格
     * eg:User 关联部门 Dept 则为: dept_id
     *
     * @param joinTableClass
     * @return BaseQuery
     */
    public BaseQuery<T> leftJoin(Class joinTableClass) {
        String joinTableName = getTableName(joinTableClass);
        String mainJoinKey = joinTableName + "_id";
        leftJoin(joinTableClass, "id", mainJoinKey);
        return this;
    }

    public BaseQuery<T> leftJoin(Class<T> clz, String joinTableKey, String mainTableKey) {
        return leftJoin(getTableName(clz), joinTableKey, mainTableKey);
    }

    public BaseQuery<T> leftJoin(Class<T> clz, String mainTableKey) {
        return leftJoin(getTableName(clz), "id", mainTableKey);
    }

    /**
     * 生成自关联语句,与 fromAs 一起使用才行,即主表的别名由 fromAs 指定.
     * 不推荐使用,因为又变成使用了硬代码.
     *
     * @param alias   关联表的别名
     * @param joinKey 关联的字段
     * @return BaseQuery
     */
    public BaseQuery<T> joinSelf(String alias, String joinKey) {
        StringBuffer join = templateMap.get("join");
        join.append(LEFTJOIN.symbol)
                .append("`")
                .append(this.mainTableName)
                .append("` ")
                .append(alias)
                .append(ON.symbol)
                .append(this.mainTableName)
                .append(".")
                .append("id")
                .append(" = ")
                .append(alias)
                .append(".")
                .append(joinKey);
        return this;
    }


    // =============== and ==============================

    /**
     * 多重子条件时使用
     */
    abstract public LambdaQuery<T> condition();

    public LambdaQuery<T> c() {
        return condition();
    }

    public BaseQuery<T> and(BaseQuery<T>... manyQe) {
        addManyCondition(AND.symbol, manyQe);
        return this;
    }

    public BaseQuery<T> or(BaseQuery<T>... manyQe) {
        addManyCondition(Opt.OR.symbol, manyQe);
        return this;
    }

    public BaseQuery<T> andEq(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.EQ, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andNe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.NE, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andLt(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.LT, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andGt(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.GT, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andLe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.LE, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andGe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.GE, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andIn(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.IN, column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andBetween(String column, Object v1, Object v2, Class... otherClz) {
        addBetween(column, v1, v2, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andLike(String column, Object val, Class... otherClz) {
        addLike(column, val, AND.name(), otherClz);
        return this;
    }

    public BaseQuery<T> andIsnull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        where.append(AND.symbol).append(column).append(ISNULL.symbol);
        return this;
    }

    public BaseQuery<T> andNotnull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        where.append(AND.symbol).append(column).append(NOTNULL.symbol);
        return this;
    }

    // =============== OR CONDITION ==============================

    public BaseQuery<T> orEq(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.EQ, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orNe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.NE, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orLt(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.LT, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orGt(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.GT, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orLe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.LE, column, val, OR.name());
        return this;
    }

    public BaseQuery<T> orGe(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.GE, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orIn(String column, Object val, Class... otherClz) {
        addWhereSql(Opt.IN, column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orBetween(String column, Object v1, Object v2, Class... otherClz) {
        addBetween(column, v1, v2, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orLike(String column, Object val, Class... otherClz) {
        addLike(column, val, OR.name(), otherClz);
        return this;
    }

    public BaseQuery<T> orIsnull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        String table = getTablePrev(otherClz);
        where.append(OR.symbol).append(table).append(".").append(column).append(ISNULL.symbol);
        return this;
    }

    public BaseQuery<T> orNotnull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        String table = getTablePrev(otherClz);
        where.append(OR.symbol).append(table).append(".").append(column).append(NOTNULL.symbol);
        return this;
    }

    public BaseQuery<T> isNull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        String table = getTablePrev(otherClz);
        where.append(table).append(".").append(column).append(ISNULL.symbol);
        return this;
    }

    public BaseQuery<T> isNotNull(String column, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        String table = getTablePrev(otherClz);
        where.append(table).append(".").append(column).append(NOTNULL.symbol);
        return this;
    }

    public BaseQuery<T> asc(String... val) {
        addOrderBy(ASC.symbol, "", val);
        return this;
    }

    public BaseQuery<T> desc(String... val) {
        addOrderBy(DESC.symbol, "", val);
        return this;
    }

    public BaseQuery<T> ascOf(String tableName, String... val) {
        addOrderBy(ASC.symbol, tableName, val);
        return this;
    }

    public BaseQuery<T> descOf(String tableName, String... val) {
        addOrderBy(DESC.symbol, tableName, val);
        return this;
    }

    private void addOrderBy(String opt, String tableName, String... val) {
        StringBuffer order = templateMap.get("order");
        for (String v : val) {
            if (order.length() == 0) {
                order.append(ORDER.symbol);
            } else {
                order.append(",");
            }
            if (S.isEmpty(tableName)) {
                order.append(v).append(opt);
            } else {
                order.append(tableName).append(".").append(v).append(opt);
            }
        }
    }

    public BaseQuery<T> group(String... columns) {
        groupOf("", columns);
        return this;
    }

    public BaseQuery<T> groupOf(String tableName, String... columns) {
        StringBuffer group = templateMap.get("group");
        group.append(GROUP.symbol);
        for (String v : columns) {
            if (S.isEmpty(tableName)) {
                group.append(v).append(",");
            } else {
                group.append(tableName).append(".").append(v).append(",");
            }
        }
        group.deleteCharAt(group.length() - 1);
        return this;
    }

    public BaseQuery<T> having(String column, Opt opt, Object val) {
        StringBuffer group = templateMap.get("group");
        StringBuffer having = templateMap.get("having");
        if (group.length() > 0) {
            having.append(HAVING.symbol)
                    .append("(")
                    .append(column)
                    .append(opt.symbol)
                    .append(val)
                    .append(")");
        }
        return this;
    }

    public BaseQuery<T> limit(long val1, long val2) {//有可能用于列表，取部分值
        StringBuffer limit = templateMap.get("limit");
        limit.append(LIMIT.symbol).append(val1).append(",").append(val2);
        return this;
    }

    protected BaseQuery<T> update(Object entity){
        return update(entity, true);
    }

    protected BaseQuery<T> update(Object entity,boolean one) {
        StringBuffer update = templateMap.get("update");
        update.append(UPDATE.symbol).append("`").append(getTableName(this.clz)).append("`").append(" SET ");
        update.append(buildUpdateBody(entity, ":_up_"));
        setDefaultWhereOfId(entity,one);
        return this;
    }

    protected BaseQuery<T> setDefaultWhereOfId(Object entity,boolean one) {
        StringBuffer where = templateMap.get("where");
        if (0 == where.length()) {
            Attr id = Attr.getRealIdAttr(entity);
            String real_id_name = id.getColumn();
            where.append(" WHERE ").append("`").append(real_id_name).append("`=");
            if (one) {
                where.append(id.getVal());
            }else {
                where.append(":").append(real_id_name);
                addParams(":"+real_id_name,id.getVal());
            }
        }
        return this;
    }

    protected BaseQuery<T> save(Object entity) {
        StringBuffer insert = templateMap.get("insert");
        insert.append(INSERT.symbol).append("`").append(getTableName(this.clz)).append("`");
        insert.append(buildInsetBody(entity, ":_add_"));
        return this;
    }

    private StringBuffer buildUpdateBody(Object entity, String keyPrev) {
        StringBuffer sql = new StringBuffer(128);
        Map<String, Object> fieldMap = BeanMapUtils.newSortMap();
        BeanMapUtils.builder().setSkipNullVal(skipNullVol).setSkipTransient(true).toUnderline().c(entity, fieldMap);
        Attr id = Attr.getRealIdAttr(entity);
        int idx = 1;
        for (String k : fieldMap.keySet()) {
            if(k.equals(id.getName())) continue;
            Object v = fieldMap.get(k);
            sql.append(k).append("=").append(keyPrev).append(k).append(",");
            String mk = keyPrev + k;
            addBodyParams(mk, fieldMap.get(k));
        }
        sql.deleteCharAt(sql.length() - 1);
        fieldMap.clear();
        return sql;
    }

    private StringBuffer buildInsetBody(Object entity, String volPrev) {
        StringBuffer sql = new StringBuffer(512);
        Map<String, Object> fieldMap = new HashMap<String, Object>(30);
        BeanMapUtils.builder().setSkipNullVal(skipNullVol).setSkipTransient(true).toUnderline().c(entity, fieldMap);
        StringBuffer keyStr = new StringBuffer(200);
        StringBuffer volStr = new StringBuffer(200);
        for (String k : fieldMap.keySet()) {
            Object v = fieldMap.get(k);
            //KEY
            keyStr.append(k).append(",");
            //VOLUME
            volStr.append(volPrev).append(k).append(",");
            addBodyParams(volPrev + k, v);

        }
        keyStr.deleteCharAt(keyStr.length() - 1);
        volStr.deleteCharAt(volStr.length() - 1);

        //VALUE()
        sql.append("(").append(keyStr).append(") VALUES (").append(volStr).append(")");
        fieldMap.clear();
        keyStr = null;
        volStr = null;
        return sql;
    }

    protected String getTableName(Class clz) {
        return Attr.getRealTableName(clz);
    }

    /**
     * 创建 limit 语句
     *
     * @param start
     * @param pageSize
     * @return
     */
    protected BaseQuery<T> settingLimit(long start, long pageSize) {
        start = PageKit.getOffsetByDbProductName(this.db_type_name, start);
        return limit(start, pageSize);
    }

    protected void clearMap(Map params) {
        if (null != params) {
            params.clear();
            params = null;
        }
    }

    /**
     * PropertyFilter 转为 Qe
     *
     * @param filterList
     * @return BaseQuery
     */
    public BaseQuery<T> whereOfFilters(List<PropertyFilter> filterList) {
        for (PropertyFilter filter : filterList) {
            if (filter.isMulti() && filter.getFilters().size() > 1) {
                builWhereOfBothFilter(this, filter);
            } else {
                builWhereOfFilter(this, filter);
            }
        }
        return this;
    }

    private BaseQuery<T> builWhereOfBothFilter(BaseQuery<T> qe, PropertyFilter filter) {
        PropertyFilter left = filter.getFilters().get(0);
        PropertyFilter rigth = filter.getFilters().get(1);
        if (filter.isRoundAnd()) {
            qe.and(builWhereOfFilter(this.c(), left), builWhereOfFilter(this.c(), rigth));
        }
        if (filter.isRoundOr()) {
            qe.or(builWhereOfFilter(this.c(), left), builWhereOfFilter(this.c(), rigth));
        }
        return qe;
    }

    private BaseQuery builWhereOfFilter(BaseQuery qe, PropertyFilter filter) {
        if (filter == null) return qe;
        String filed = StringKit.toUnderline(filter.getFieldName());
        PropertyFilter.MatchType matchType = filter.getMatchType();
        Object[] vv = filter.getValues();
        Object v1 = null;
        Object v2 = null;
        if (vv.length == 1) {
            if (null == vv[0] && matchType != PropertyFilter.MatchType.ISNULL && matchType != PropertyFilter.MatchType.NOTNULL)
                return qe;
            v1 = StringKit.toUnderline(String.valueOf(vv[0]));
        }
        if (vv.length == 2) {
            v2 = StringKit.toUnderline(String.valueOf(vv[1]));
        }
        switch (matchType) {
            case EQ:
                return qe = (filter.isAnd()) ? andEq(filed, v1) : orEq(filed, v1);
            case NE:
                return qe = (filter.isAnd()) ? andNe(filed, v1) : orNe(filed, v1);
            case LE:
                return qe = (filter.isAnd()) ? andLe(filed, v1) : orLe(filed, v1);
            case GE:
                return qe = (filter.isAnd()) ? andGe(filed, v1) : orGe(filed, v1);
            case GT:
                return qe = (filter.isAnd()) ? andGt(filed, v1) : orGt(filed, v1);
            case LT:
                return qe = (filter.isAnd()) ? andLt(filed, v1) : orLt(filed, v1);
            case ISNULL:
                return qe = (filter.isAnd()) ? andIsnull(filed) : orIsnull(filed);
            case NOTNULL:
                return qe = (filter.isAnd()) ? andNotnull(filed) : orNotnull(filed);
            case IN:
                return qe = (filter.isAnd()) ? andIn(filed, v1) : orIn(filed, v1);
            case BETWEEN:
                return qe = (filter.isAnd()) ? andBetween(filed, v1, v2) : orBetween(filed, v1, v2);
            case LIKE:
                return qe = (filter.isAnd()) ? andLike(filed, v1) : orLike(filed, v1);
            case LIKESTART:
                return qe = (filter.isAnd()) ? andLike(filed, v1) : orLike(filed, v1);
            case LIKEANYWHERE:
                return qe = (filter.isAnd()) ? andLike(filed, v1) : orLike(filed, v1);
            case LIKEISTART:
                return qe = (filter.isAnd()) ? andLike(filed, v1) : orLike(filed, v1);
            case LIKEIANYWHERE:
                return qe = (filter.isAnd()) ? andLike(filed, v1) : orLike(filed, v1);
        }
        return qe;
    }

    private void addWhereSql(Opt opt, String k, Object val, String link, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        this.link = link;
        if (null == val || "".equals(val)) return;
        if (where.length() > 0 && !onlyConition) {
            where.append(link);
        }
        StringBuffer key = new StringBuffer().append(":").append(link).append(opt.name()).append("_").append(k);
        //
        if (!onlyConition) {
            where.append("(");//start
        }
        if (k.indexOf(".") != -1) {// 手动指定了表名,eg: t1.age = 18
            where.append(k);
        } else {
            String table = getTablePrev(otherClz);
            where.append(table).append(".").append(k);
        }
        if (Opt.IN.equals(opt)) {// IN 语句加(), EG: stat IN(1,2)
            where.append(opt.symbol).append("(").append(key).append(")");
        } else {
            where.append(opt.symbol).append(key);
        }
        if (!onlyConition) {//end
            where.append(")");
        }
        //
        val = buildSubSelect(val);
        //
        String p_key = key.toString();
        addParams(p_key, val);
    }

    /**
     * 子查询，直接生成最终SQL，并且不作SQL注入检查
     * @param val
     * @return
     */
    private Object buildSubSelect(Object val) {
        if (val instanceof BaseQuery) {
            this.checkSqlHack = false;
            return "(" + ((BaseQuery) val).build() + ")";
        }
        return val;
    }

    private String getTablePrev(Class[] otherClz) {
        String table = mainTableName;
        if (null != otherClz && otherClz.length > 0) {
            table = getTableName(otherClz[0]);
        }
        return table;
    }

    private void addParams(String key, Object v1) {
        this.where_params.put(key, v1);
    }
    private void addBodyParams(String key, Object v1) {
        this.body_params.put(key, v1);
    }


    public Map<String, Object> toJdbcParams(Map<String, Object> params) {
        Map<String, Object> jMap = new HashMap<>(params.size());
        for (String k : params.keySet()) {
            Object v = params.get(k);
            k = StringKit.replace(k, ":", "");
            jMap.put(k, v);
        }
        return jMap;
    }

    private void addBetween(String column, Object v1, Object v2, String link, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        this.link = link;
        if (null == v1 || "".equals(v1)) return;
        if (null == v2 || "".equals(v2)) return;
        StringBuffer key1 = new StringBuffer().append(":").append(link.toLowerCase()).append("_between_").append(column).append("_v1");
        StringBuffer key2 = new StringBuffer().append(":").append(link.toLowerCase()).append("_between_").append(column).append("_v2");
        String table = getTablePrev(otherClz);
        if (where.length() > 0) {
            where.append(link);
        }
        where.append("(")
                .append(table).append(".").append(column)
                .append(BETWEEN.symbol)
                .append(key1).append(" AND ").append(key2);
        where.append(")");
        addParams(key1.toString(), v1);
        addParams(key2.toString(), v2);
        key1 = null;
        key2 = null;
    }

    private void addLike(String column, Object val, String link, Class... otherClz) {
        StringBuffer where = templateMap.get("where");
        this.link = link;
        if (null == val || "".equals(val)) return;
        if (where.length() > 0) {
            where.append(link);
        }
        String table = getTablePrev(otherClz);
        StringBuffer key = new StringBuffer().append(":").append(link.toLowerCase()).append("_like_").append(column);
        where.append("(")
                .append(LIKE.symbol).append("(")
                .append(table).append(".").append(column)
                .append(",")
                .append(key)
                .append(")>0) ");
        addParams(key.toString(), val);
        key = null;
    }

    private void addManyCondition(String link, BaseQuery... manyQe) {
        StringBuffer where = templateMap.get("where");
        if (where.length() > 0) {
            where.append(Opt.AND).append("(");
        } else {
            where.append("(");
        }
        if (manyQe.length > 0) {
            for (int i = 0; i < manyQe.length; i++) {
                BaseQuery qe = manyQe[i];
                String sql = QueryKit.settingParams(qe.templateMap.get("where").toString(), qe.where_params,checkSqlHack);
                if (i == 0) {
                    sql = StringKit.replace(sql, "AND(", "(");
                    sql = StringKit.replace(sql, "OR(", "(");
                }
                if (i > 0) {
                    if (sql.indexOf("AND(") == -1 && sql.indexOf("OR(") == -1) {
                        where.append(link).append(sql);
                        continue;
                    }
                }
                where.append(sql);
            }
        }
        where.append(") ");
    }

    public enum Opt {
        EQ("="),
        NE("!="),
        LT("<"),
        GT(">"),
        LE("<="),
        GE(">="),
        IN(" IN "),
        ISNULL(" IS NULL"),
        NOTNULL(" IS NOT NULL"),
        BETWEEN(" BETWEEN "),
        LIKE("LOCATE "),
        SELECT("SELECT "),
        FROM(" FROM "),
        LEFTJOIN(" LEFT JOIN "),
        ON(" ON "),
        WHERE(" WHERE "),
        DEL("DELETE "),
        AS(" AS "),
        ORDER(" ORDER BY "),
        GROUP(" GROUP BY "),
        HAVING(" HAVING "),
        ASC(" ASC"),
        DESC(" DESC"),
        LIMIT(" LIMIT "),
        AND(" AND "),
        OR(" OR "),
        UPDATE(" UPDATE "),
        INSERT(" INSERT INTO ");

        public String symbol;

        Opt(String symbol) {
            this.symbol = symbol;
        }
    }


    private StringBuffer buildWhereSql() {
        StringBuffer where = templateMap.get("where");
        if (where.length() > 0 && where.indexOf("WHERE") == -1) {
            where.insert(0, "(");
            where.insert(0, WHERE.symbol);
            where.append(")");
        }
        return where;
    }

    public String buildSymbolsql() {
        if (isReadSql()) {
            buildSelect();
            buildFrom();
        } else {
            templateMap.remove("select");
            templateMap.remove("from");
        }
        if (!onlyConition) {
            buildWhereSql();
        }
        return templateMapToStr();
    }

    public String buildCountSymbolSql() {//COUNT 语句独立构建，因为getPage里包含了Count语句有冲突
        StringBuffer _t = new StringBuffer(300)
                .append("SELECT COUNT(1) ")
                .append(buildFrom())
                .append(buildWhereSql());
        for (String key :  templateMap.keySet()) {
            if (key.equals("select") || key.equals("from") || key.equals("where")) {
                continue;
            } else {
                _t.append(templateMap.get(key));
            }
        }
        return _t.toString();
    }


    private String templateMapToStr() {
        StringBuffer _t = new StringBuffer(300);
        templateMap.forEach((k, v) -> _t.append(v));
        return _t.toString();
    }

    public BaseQuery<T> debug() {
        this.show_debug_sql = true;
        return this;
    }

    public BaseQuery printSql(String sql) {
        if (show_debug_sql) {
            PrintUtil.yellowln("DEBUG SQL:" );
            PrintUtil.greenln(sql );
            printParams(this.body_params);
            printParams(this.where_params);
        }
        return this;
    }

    public String build() {
        String sql = settingParams(buildSymbolsql());
        return sql;
    }

    private boolean isReadSql() {
        StringBuffer insert = templateMap.get("insert");
        StringBuffer update = templateMap.get("update");
        StringBuffer del = templateMap.get("del");
        return (0 == update.length() && 0 == insert.length() && 0 == del.length());
    }

    private StringBuffer buildSelect() {
        StringBuffer select = templateMap.get("select");
        if (null != select && select.length() == 0) {
            select.append(" SELECT * ");
        }
        return select;
    }

    public String settingParams(String symbolsql) {
        return QueryKit.settingParams(symbolsql, this.where_params,checkSqlHack);
    }

    private StringBuffer buildFrom() {
        StringBuffer from = templateMap.get("from");
        if (null != from && 0 == from.length()) {
            from.append(FROM.symbol);
            if (null != mainTableName && !"".equals(mainTableName)) {
                from.append("`").append(mainTableName).append("`").append(" AS ").append(mainTableName).append(" ");
            }
        }
        return from;
    }

    public BaseQuery checkHack() {
        checkSqlHack = true;
        return this;
    }
    public BaseQuery checkHack(boolean b) {
        checkSqlHack = b;
        return this;
    }

    public void clear() { // 手动清理内存是种好习惯 :)
        init();
        clearMap(where_params);

    }

    private static String replaceTag(String sql, String tag, StringBuffer content) {
        if (content.length() > 0) {
            if (String.valueOf(content.charAt(content.length() - 1)).equals(",")) {
                content.deleteCharAt(content.length() - 1);
            }
            sql = StringKit.replace(sql, tag, content.toString());
        } else {
            sql = StringKit.replace(sql, tag, "");
        }
        return sql;
    }

    private void init() {
        symbolsql = "";
        String link = null;
        show_debug_sql = false;
        onlyConition = false; //只限 CONDITION 语句
        initTemplateMap();
    }

    private void printParams(Map<String,Object> paramsMap){
        if (paramsMap != null && paramsMap.size() > 0) {
            StringBuffer t = new StringBuffer(300);
            for (String key : paramsMap.keySet()) {
                t.append(key).append(":").append(paramsMap.get(key).toString());
            }
            PrintUtil.yellow("[PARAMS]:");
            PrintUtil.cyanLn(t.toString());
            t=null;
        }
    }

    private Map<String, StringBuffer> initTemplateMap() {
        templateMap = new LinkedHashMap<>(16);
        templateMap.put("insert", new StringBuffer(300));
        templateMap.put("update", new StringBuffer(300));
        templateMap.put("del", new StringBuffer(100));
        templateMap.put("select", new StringBuffer(200));
        templateMap.put("from", new StringBuffer(50));
        templateMap.put("join", new StringBuffer(200));
        templateMap.put("where", new StringBuffer(300));
        templateMap.put("subselect", new StringBuffer(300));
        templateMap.put("group", new StringBuffer(200));
        templateMap.put("having", new StringBuffer(100));
        templateMap.put("order", new StringBuffer(100));
        templateMap.put("limit", new StringBuffer(50));
        return templateMap;
    }


    public static void main(String[] args) throws Exception {
/*        List<String> strList = Arrays.asList("a1", "a2");
        StringBuffer sb = new StringBuffer();
        sb.append("?").append(strList.get(0));
        strList.stream().skip(1).forEach(s -> sb.append("&").append(s));
        System.err.println(sb.toString());*/

        Qe q = new Qe().test();
        q.templateMap.put("from", new StringBuffer("from user"));
        System.err.println(q.buildCountSymbolSql());
    }

}
